import sqlite3 
import json
import time
from docx import Document
import random
from docx.shared import Cm,Pt
from docx.oxml import OxmlElement
from docx.oxml.ns import qn
import os
db = "dictation.db"
dictDb = "dict.db"
def getTableFields(cur,tableName):
	cur.execute("PRAGMA table_info(%s)"%(tableName))
	ret = ()
	for row in cur:
		ret = ret +(row[1],)
	return ret
def readTable(cur,tableName):
	cur.execute("select * from %s"%(tableName))
	return cur.fetchall()
def readTableW(cur,tableName,where):
	cur.execute("select * from %s where %s"%(tableName,where))
	return cur.fetchall()
def readTableF(cur,tableName):
	fields = getTableFields(cur,tableName)
	lf = len(fields)
	ret = []
	for row in readTable(cur,tableName):
		tmp = {}
		for i in range(lf):
			tmp[fields[i]] = row[i]
		ret.append(tmp)
	return ret
def deleteTableW(conn,cur,tableName,where):
	cur.execute("delete from %s where %s"%(tableName,where))
	conn.commit()
def readTableFW(cur,tableName,where):
	fields = getTableFields(cur,tableName)
	lf = len(fields)
	ret = []
	for row in readTableW(cur,tableName,where):
		tmp = {}
		for i in range(lf):
			tmp[fields[i]] = row[i]
		ret.append(tmp)
	return ret
def checkTableLink(cur,tableName):
	cur.execute("select * from mgr where tableName='%s'"%(tableName))
	if len(cur.fetchall()) == 0:
		return False
	else:
		return True
def addTableLink(conn,cur,tableName,type):
	cur.execute("insert into mgr (tableName,type) values ('%s',%d)"%(tableName,type))
	conn.commit()
def convert(conn,cur):
	createHistoryTable(cur)
	for table in readTableF(cur,"mgr"):
		sourceList = []
		id = 0
		for row in readTableF(cur,table["tableName"]):
			sourceList += json.loads(row["sourceItem"])
			for historyList in json.loads(row["history"]):
				id = id+1
				for historyItem in historyList:
					cur.execute("insert into history (tableName,destId,pass,time)values(?,?,?,?)",(table["tableName"],id,historyItem["pass"],historyItem["time"]))
		conn.commit()
		createSourceTable(cur,table["tableName"]+"_converted")
		insertSourceList(conn,cur,table["tableName"]+"_converted",sourceList)
def insertSourceList(conn,cur,tableName,sourceList):
	for sourceItem in sourceList:
		if(sourceItem["cover"] == False):
			sourceItem.pop("cover")
			cur.execute("insert into %s (sourceItem) values ('%s')"%(tableName,json.dumps(sourceItem).encode("utf-8").decode("unicode-escape")))
		else:
			sourceItem.pop("cover")
			coverTableName = sourceItem["tableName"]
			coverId = sourceItem["id"]
			sourceItem.pop("id")
			sourceItem.pop("tableName")
			cur.execute("delete from %s where id=%d"%(coverTableName,coverId))
			cur.execute("insert into %s (sourceItem) values ('%s')"%(tableName,json.dumps(sourceItem).encode("utf-8").decode("unicode-escape")))
	conn.commit()
def decodeAllPinyin(conn,cur):
	for row in readTableF(cur,"mgr"):
		for r in readTableF(cur,row["tableName"]):
			cur.execute("update %s set sourceItem='%s' where id=%d"%(row["tableName"],r["sourceItem"].encode("utf-8").decode("unicode-escape"),r["id"]))
	conn.commit()
def getSourceList(cur,tableName):
	sourceList = []
	for row in readTableF(cur,tableName):
		sourceItem = json.loads(row["sourceItem"])
		sourceItem["id"] = row["id"]
		sourceItem["t"] = tableName
		sourceList.append(sourceItem)
	return sourceList
def getUnsolvedSourceList(cur,tableName):
	sourceList = []
	for row in readTableF(cur,tableName):
		sourceItem = json.loads(row["sourceItem"])
		id = sourceItem["id"] = row["id"]
		t = sourceItem["t"] = tableName
		if len(readTableFW(cur,"history","destId=%d and tableName='%s'"%(id,t))) == 0:
			sourceList.append(sourceItem)
	return sourceList
def getFaultSourceList(cur,tableName):
	sourceList = []
	for row in readTableF(cur,tableName):
		isFault = False
		sourceItem = json.loads(row["sourceItem"])
		id = sourceItem["id"] = row["id"]
		t = sourceItem["t"] = tableName
		for rowHis in readTableFW(cur,"history","destId=%d and tableName='%s'"%(id,t)):
			if rowHis["pass"] == False:
				isFault = True
				break
		if isFault == True:
			sourceList.append(sourceItem)
	return sourceList
def getLimitedSourceList(sourceList,count):
	count = min(len(sourceList),count)
	retList = []
	for i in range(count):
		j = random.randint(0,len(sourceList)-1)
		retList.append(sourceList[j])
		sourceList.pop(j)
	return retList
def genRecordList(sourceList):
	recordList = []
	for sourceItem in sourceList:
		tmp = {}
		tmp["t"] = sourceItem["t"]
		tmp["id"] = sourceItem["id"]
		recordList.append(tmp)
	return recordList
def writeTestDoc(sourceListList,fileName,tableNameList):
	#两种类型测试时都一样
	if not os.path.exists("docs"):
		os.mkdir("docs")
	document = Document()
	s = document.sections[0]
	s.top_margin = Cm(1)
	s.bottom_margin = Cm(1)
	s.left_margin = Cm(1)
	s.right_margin = Cm(1)
	head=document.sections[0].header
	head.add_paragraph(fileName+"\n生成时间："+ time.strftime("%Y-%m-%d %H:%M:%S"))
	for sourceList,tableName in zip(sourceListList,tableNameList):
		document.add_paragraph(tableName)
		p = document.add_paragraph("")
		for item,j in zip(sourceList,range(len(sourceList))):
			p.add_run("["+str(j+1)+"]")
			index=0
			l = len(item["han"])
			for i in item["pinyin"]:
				indexPy = i["i"]
				if index<indexPy:
					p.add_run(item["han"][index:indexPy])
				p.add_run(item["han"][indexPy]).underline = True
				index = indexPy+1
			if index <l:
				p.add_run(item["han"][index:])
			for i in item["pinyin"]:
				p.add_run(i["py"]+" ")
	document.save("docs/"+fileName+".docx")
def writeDescDoc(sourceListList,fileName,tableNameList):
	conn = sqlite3.connect(dictDb)
	memDb =sqlite3.connect(":memory:")
	conn.backup(memDb)
	cur = memDb.cursor()
	if not os.path.exists("docs"):
		os.mkdir("docs")
	document = Document()
	s = document.sections[0]
	s.top_margin = Cm(1.27)
	s.bottom_margin = Cm(1.27)
	s.left_margin = Cm(1.27)
	s.right_margin = Cm(1.27)
	head=document.sections[0].header
	head.add_paragraph(fileName+" 类型：释义\n生成时间："+ time.strftime("%Y-%m-%d %H:%M:%S"))
	for sourceList,tableName in zip(sourceListList,tableNameList):
		document.add_paragraph(tableName)
		p = document.add_paragraph("")
		for item,j in zip(sourceList,range(len(sourceList))):
			p.add_run("["+str(j+1)+"]")
			query = readTableFW(cur,"xdDict","han like '%s'"%("%"+item["han"]+"%"))
			if len(query) != 0:
				p.add_run(query[0]["description"]+"\n")
			else:
				query = readTableFW(cur,"cyDict","han like '%s'"%("%"+item["han"]+"%"))
				if len(query) != 0:
					p.add_run(query[0]["description"]+"\n")
				else:
					p.add_run("【%s】\n"%item["han"])
	document.save("docs/"+fileName+".docx")
def genMulDoc(sourceList,fileName,type):
	if not os.path.exists("docs"):
		os.mkdir("docs")
	Q = Document()
	sQ = Q.sections[0]
	sQ.top_margin = Cm(1.27)
	sQ.bottom_margin = Cm(1.27)
	sQ.left_margin = Cm(1.27)
	sQ.right_margin = Cm(1.27)

	typeTag = {}
	typeTag[0] = "拼音文档"
	typeTag[1] = "字形文档"
	headQ=Q.sections[0].header
	footQ=Q.sections[0].footer
	footQ.add_paragraph().add_run("整理:杭州四中高三十班 传播请遵守知识共享(Creative Commons) 署名-非商业性使用-相同方式共享 3.0 协议\n代码仓库https://gitee.com/TonyZhangZTN/gaokaochinese 遵循MIT LICENSE").font.size = Pt(6)
	sectPr = Q.sections[0]._sectPr
	cols = sectPr.xpath('./w:cols')[0]
	cols.set(qn('w:num'),'4')
	headQ.add_paragraph("文档名称："+fileName+"\n生成时间："+ time.strftime("%Y-%m-%d %H:%M:%S")).font.size = Pt(6)
	headQ.add_paragraph("测试个数："+str(len(sourceList))+" 类型:"+typeTag[type]).font.size = Pt(6)
	pQ = Q.add_paragraph()
	mapItem = {}
	for item in sourceList:
		#mapItem[字][拼音][0,1,2,3...] = item
		for i in item["pinyin"]:
			if not item["han"][i["i"]] in mapItem :
				mapItem[item["han"][i["i"]]] = {}
			if not i["py"] in mapItem[item["han"][i["i"]]] :
				mapItem[item["han"][i["i"]]][i["py"]] = []
			mapItem[item["han"][i["i"]]][i["py"]].append(item)
	#每个汉字内拼音排序//TODO
	for mi in mapItem:
		pass
	def comp1(i):
		return tuple(i[1].keys())[0]
	for keyHan,i in zip(sorted(mapItem.items(),key=comp1),range(len(sorted(mapItem.items(),key=comp1)))):
		pQ.add_run("["+str(i+1)+"]")
		pQ.add_run(keyHan[0]+"\n")
		for py in sorted(mapItem[keyHan[0]].keys()):
			pQ.add_run(py+":")
			for item,j in zip(mapItem[keyHan[0]][py],range(len(mapItem[keyHan[0]][py]))):
				index = 0
				l = len(item["han"])
				for i in item["pinyin"]:
					indexPy = i["i"]
					if index<indexPy:
						pQ.add_run(item["han"][index:indexPy])
					if keyHan[0] == item["han"][indexPy]:
						pQ.add_run(item["han"][indexPy]).underline = True
					else:
						pQ.add_run(item["han"][indexPy]+"("+i["py"]+")")
					index = indexPy + 1
				if index < l:
					pQ.add_run(item["han"][index:])
				pQ.add_run(",")
			pQ.add_run("\n")
	Q.save("docs/[梳理][%s]%s.docx"%(typeTag[type],fileName))
def genSourceQADoc(pyList,fileName,type):
	if not os.path.exists("docs"):
		os.mkdir("docs")
	Q = Document()
	A = Document()
	sQ = Q.sections[0]
	sA = A.sections[0]
	sQ.top_margin = Cm(1.27)
	sQ.bottom_margin = Cm(1.27)
	sQ.left_margin = Cm(1.27)
	sQ.right_margin = Cm(1.27)
	sA.top_margin = Cm(1.27)
	sA.bottom_margin = Cm(1.27)
	sA.left_margin = Cm(1.27)
	sA.right_margin = Cm(1.27)

	typeTag = {}
	typeTag[0] = "拼音练习"
	typeTag[1] = "字形练习"
	headQ=Q.sections[0].header
	headQ.add_paragraph("文档名称："+fileName+"\n生成时间："+ time.strftime("%Y-%m-%d %H:%M:%S"))
	headQ.add_paragraph("测试个数："+str(len(pyList))+" 类型:"+typeTag[type])
	pQ = Q.add_paragraph()
	pA = A.add_paragraph()
	for item,j in zip(pyList,range(len(pyList))):
		pQ.add_run("["+str(j+1)+"]").font.size = Pt(6)
		pA.add_run("["+str(j+1)+"]").font.size = Pt(6)
		index = 0
		l = len(item["han"])
		for i in item["pinyin"]:
			indexPy = i["i"]
			if index<indexPy:
				pQ.add_run(item["han"][index:indexPy])
			if type == 0:
				pQ.add_run(item["han"][indexPy]).underline = True
			else:
				pQ.add_run(i["py"])
			index = indexPy + 1
		if index <l:
			pQ.add_run(item["han"][index:])
		if type == 0:
			pQ.add_run("________")
			for i in item["pinyin"]:
				pA.add_run(i["py"]+" ")
		else:
			for i in item["pinyin"]:
				pQ.add_run("___")
				pA.add_run(item["han"][i["i"]]+" ")

	Q.save("docs/[原卷][%s]%s.docx"%(typeTag[type],fileName))
	A.save("docs/[答案][%s]%s.docx"%(typeTag[type],fileName))
def createTableMgr(cur):
	cur.execute("""
		CREATE TABLE IF NOT EXISTS mgr(
		`id` INTEGER PRIMARY KEY AUTOINCREMENT,
		`tableName` CHAR(32),
		`type` INTEGER
		)
		""")
def createSourceTable(cur,tableName):
	cur.execute("""
		CREATE TABLE IF NOT EXISTS %s(
		`id` INTEGER PRIMARY KEY AUTOINCREMENT,
		`sourceItem` TEXT
		)
		"""%(tableName))
def createTaskTable(cur):
	cur.execute("""
		CREATE TABLE IF NOT EXISTS `task`(
		`id` INTEGER PRIMARY KEY AUTOINCREMENT,
		`recordList` TEXT,
		`count` INTEGER,
		`filename` CHAR(64),
		`type` INTEGER
		)
		""")
def createHistoryTable(cur):
	cur.execute("""
		CREATE TABLE IF NOT EXISTS `history`(
		`id` INTEGER PRIMARY KEY AUTOINCREMENT,
		`tableName` CHAR(32),
		`destId` INTEGER,
		`pass` BOOLEAN,
		`time` DATETIME
		)
		""")
def createDictTable(cur,tableName):
	cur.execute("""
		CREATE TABLE IF NOT EXISTS `%s`(
		`id` INTEGER PRIMARY KEY AUTOINCREMENT,
		`han` VARCHAR(32),
		`description` TEXT
		)
		"""%(tableName))
def clearAllHistory(conn,cur):
	createHistoryTable(cur)
	cur.execute("drop table history")
	conn.commit()
	createHistoryTable(cur)
def genTask(conn,cur,recordList,fileName,type):
	cur.execute("insert into task (recordList,count,filename,type)values('%s',%d,'%s',%d)"%(json.dumps(recordList),len(recordList),fileName,type))
	conn.commit()
def updateSourceState(conn,cur,recordList,recordId):
	tm = time.strftime("%Y-%m-%d %H:%M:%S")
	for recordItem in recordList:
		t = recordItem["t"]
		id = recordItem["id"]
		cur.execute("insert into history (tableName,destId,pass,time)values(?,?,?,?)",(t,id,recordItem["pass"],tm))
	conn.commit()
	deleteTableW(conn,cur,"task","id=%d"%recordId)
def importDict(conn,cur,dictFilename,tableName):
	for line in open(dictFilename,"r",encoding="UTF-8").readlines():
		if line[0] == "【" :
			lineStr = line.strip("\n")
			cur.execute("insert into %s (han,description)values(?,?)"%tableName,(lineStr[1:lineStr.find("】")],lineStr))
	conn.commit()